﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using DevExpress.XtraGrid.Views.Grid;

namespace Print_UI
{
    public partial class FrmUpdateExtraImforStudentDepartment : CommonForm
    {
        public FrmUpdateExtraImforStudentDepartment()
        {
            InitializeComponent();
        }
        DataTable tbExcelFile = new DataTable();
        DataTable tbStudent = new DataTable();
        DataTable tbClass = new DataTable();
        DataTable tbStudentINsemester = new DataTable();
        public int iYearID, iDepartmentID, iSemesterID, iResulf;
        private SqlConnection _Cnn;
        public SqlConnection Cnn
        {
            get { return _Cnn; }
            set { _Cnn = value; }
        }
        private void FrmUpdateExtraImforStudentDepartment_Load(object sender, EventArgs e)
        {
            try
            {
                tbExcelFile.Columns.Add("Check", typeof(bool));
                tbExcelFile.Columns.Add("Note", typeof(string));
                tbExcelFile.Columns.Add("StudentCode", typeof(string));
                tbExcelFile.Columns.Add("StudentFName", typeof(string));
                tbExcelFile.Columns.Add("StudentLName", typeof(string));
                tbExcelFile.Columns.Add("Class", typeof(string));
                tbExcelFile.Columns.Add("ClassID", typeof(int));
                tbExcelFile.Columns.Add("StudentID", typeof(int));
                tbExcelFile.Columns.Add("SemesterAverage", typeof(string));

                Cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString);
                SqlDataAdapter DaStudent = new SqlDataAdapter();
                DaStudent.SelectCommand = new SqlCommand("SELECT * from PRI_Student where DepartmentID = @DepartmentID  ", Cnn);
                DaStudent.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepartmentID;
                DaStudent.Fill(tbStudent);

                //get data from Class table 
                SqlDataAdapter DaClass = new SqlDataAdapter();
                DaClass.SelectCommand = new SqlCommand("SELECT CAT_Class.ID , CAT_Class.Code from CAT_Class where DepartmentID = @DepartmentID", Cnn);
                DaClass.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepartmentID;
                DaClass.SelectCommand.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearID;
                DaClass.Fill(tbClass);
                //get data from StudentInSemester table
                SqlDataAdapter DaStudentSemester = new SqlDataAdapter();
                DaStudentSemester.SelectCommand = new SqlCommand("select * From PRI_StudentInSemester  where DepartmentID=@DepartmentID and YearID = @YearID and SemesterID=@SemesterID", Cnn);
                DaStudentSemester.SelectCommand.Parameters.Add("@DepartmentID", SqlDbType.Int).Value = iDepartmentID;
                DaStudentSemester.SelectCommand.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                DaStudentSemester.SelectCommand.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearID;
                DaStudentSemester.Fill(tbStudentINsemester);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnChooseFile_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                OpenFileDialog Filechoose = new OpenFileDialog();
                tbExcelFile.Rows.Clear();
                if (Filechoose.ShowDialog() == DialogResult.OK)
                {
                    string sFilePath = Filechoose.FileName;
                    HSSFWorkbook workbook = new HSSFWorkbook((new FileStream(sFilePath, FileMode.Open, FileAccess.Read)));
                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        // excute loop to get data from excel file bu using NPOI references
                        for (int row = 1; row <= sheet.LastRowNum; row++)
                        {
                            if (sheet.GetRow(row) != null)
                            {
                                string sStudentCode = string.Empty;
                                if (sheet.GetRow(row).GetCell(0) != null)
                                {
                                    sheet.GetRow(row).GetCell(0).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(0).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentCode = sheet.GetRow(row).GetCell(0).StringCellValue;

                                    }
                                }
                                string sStudentFname = string.Empty;
                                if (sheet.GetRow(row).GetCell(1) != null)
                                {
                                    sheet.GetRow(row).GetCell(1).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(1).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentFname = sheet.GetRow(row).GetCell(1).StringCellValue;

                                    }
                                }
                                string sStudentLname = string.Empty;
                                if (sheet.GetRow(row).GetCell(2) != null)
                                {
                                    sheet.GetRow(row).GetCell(2).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(2).StringCellValue.ToString() != string.Empty)
                                    {
                                        sStudentLname = sheet.GetRow(row).GetCell(2).StringCellValue;

                                    }
                                }
                                string sSemesterAverage = string.Empty;
                                if (sheet.GetRow(row).GetCell(3) != null)
                                {
                                    sheet.GetRow(row).GetCell(3).SetCellType(CellType.String);
                                    if (sheet.GetRow(row).GetCell(3).StringCellValue.ToString() != string.Empty)
                                    {
                                        sSemesterAverage = sheet.GetRow(row).GetCell(3).StringCellValue;

                                    }
                                }
                                string sClass = string.Empty;
                                if (sheet.GetRow(row).GetCell(4) != null)
                                {
                                    sheet.GetRow(row).GetCell(4).SetCellType(CellType.String);

                                    if (sheet.GetRow(row).GetCell(4).StringCellValue.ToString() != string.Empty)
                                    {
                                        sClass = sheet.GetRow(row).GetCell(4).StringCellValue;

                                    }
                                }
                                DataRow drcheck = tbExcelFile.NewRow();
                                //Add Datarow for Excel datatable.
                                drcheck["Check"] = true;
                                drcheck["Note"] = string.Empty;
                                drcheck["StudentID"] = -1;
                                drcheck["ClassID"] = -1;
                                drcheck["StudentCode"] = sStudentCode;
                                drcheck["Class"] = sClass;
                                drcheck["SemesterAverage"] = sSemesterAverage;

                                if (sStudentCode != string.Empty && sClass != string.Empty && sStudentFname != string.Empty && sStudentLname != string.Empty)
                                {
                                    drcheck["StudentFName"] = sStudentFname;
                                    drcheck["StudentLName"] = sStudentLname;
                                    // Select query on datatable for comparing with database Student.
                                    string expression = "Code = '" + sStudentCode + "'";
                                    DataRow[] foundRowsStudent;
                                    foundRowsStudent = tbStudent.Select(expression);
                                    if (foundRowsStudent.Length == 0)
                                    {
                                        drcheck["Check"] = false;
                                        drcheck["Note"] = "Sinh viên không tồn tại";

                                    }
                                    else
                                    {
                                        int iStudentID = Convert.ToInt32(foundRowsStudent[0]["ID"]);
                                        drcheck["StudentID"] = iStudentID;
                                        // Select query on datatable for comparing with database Class.
                                        string expression1 = "Code = '" + sClass + "'";
                                        DataRow[] foundRowsClass;
                                        foundRowsClass = tbClass.Select(expression1);

                                        if (foundRowsClass.Length == 0)
                                        {
                                            drcheck["Check"] = false;
                                            // drcheck["Note"] = "Lớp không tồn tại";
                                        }
                                        else if (foundRowsClass.Length > 0)
                                        {
                                            int iClassID = Convert.ToInt32(foundRowsClass[0]["ID"]);
                                            drcheck["ClassID"] = iClassID;
                                            string expression3 = "StudentID = '" + iStudentID + "' AND ClassID = '" + iClassID + "'";
                                            DataRow[] foundRowsFee;
                                            foundRowsFee = tbStudentINsemester.Select(expression3);
                                            if (foundRowsFee.Length != 0)
                                            {
                                                drcheck["Check"] = true;
                                            }
                                            else
                                            {
                                                drcheck["Check"] = false;
                                                // assign IDStudent had been exists in StudentinSubject.
                                                drcheck["Note"] = "Sinh viên không tồn tại";
                                            }

                                        }
                                    }

                                }
                                else
                                {
                                    drcheck["Check"] = false;
                                    drcheck["Note"] = "Thiếu thông tin";

                                }

                                // add data row into datatables. 
                                tbExcelFile.Rows.Add(drcheck);

                            }

                        }

                        grdimportdata.DataSource = tbExcelFile;

                    }
                }

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                Cnn.Open();
                if (gridView1.RowCount == 0)
                {
                    MessageBox.Show("Không có dữ liệu", "Vui lòng kiểm tra lại", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                }
                else
                {

                    string sSelect = "Check= 1";
                    DataRow[] drRow = tbExcelFile.Select(sSelect);
                    if (drRow.Length == 0)
                    {
                        MessageBox.Show("Dữ liệu không hợp lệ,Vui lòng kiểm tra lại", "Cảnh báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
                    }

                    else if (drRow.Length > 0)
                    {
                        // using loop to update schoolfee
                        for (int irow = 0; irow < drRow.Length; irow++)
                        {
                            SqlCommand cmmUpdateStundentInSemester = new SqlCommand("Update  PRI_StudentInSemester Set SemesterAverage= @SemesterAverage,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy Where StudentID=@StudentID and YearID = @YearID and SemesterID=@SemesterID ", Cnn);
                            cmmUpdateStundentInSemester.Parameters.Add("@StudentID", SqlDbType.Int).Value = Convert.ToInt32(drRow[irow]["StudentID"]);
                            cmmUpdateStundentInSemester.Parameters.Add("@YearID", SqlDbType.Int).Value = iYearID;
                            cmmUpdateStundentInSemester.Parameters.Add("@SemesterID", SqlDbType.Int).Value = iSemesterID;
                            cmmUpdateStundentInSemester.Parameters.Add("@SemesterAverage", SqlDbType.Float).Value = (String.Format("{0:00.0}", drRow[irow]["SemesterAverage"]));
                            cmmUpdateStundentInSemester.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = DateTime.Now;
                            cmmUpdateStundentInSemester.Parameters.Add("@ModifiedBy", SqlDbType.NVarChar).Value = UserName;
                            iResulf = cmmUpdateStundentInSemester.ExecuteNonQuery();

                        }
                        if (iResulf > 0)
                        {
                            MessageBox.Show("Cập nhật thành công");
                            this.DialogResult = DialogResult.OK;
                            this.Close();
                            Cnn.Close();
                        }
                        else
                        {
                            MessageBox.Show("Cập nhật thất bại ", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                        }

                    }


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Cnn.Close();
            }
        }

        private void btnCancle_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (MessageBox.Show("Thoát giao diện này", "Thông báo", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                this.Close();
            }
        }

        private void gridView1_RowStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowStyleEventArgs e)
        {
            GridView View = sender as GridView;
            if (e.RowHandle >= 0)
            {
                string sCheck = Convert.ToString(View.GetRowCellDisplayText(e.RowHandle, View.Columns["Note"]));
                if (sCheck == "Thiếu thông tin")
                {
                    e.Appearance.BackColor = Color.Salmon;

                }
                if (sCheck == "Sinh viên không tồn tại")
                {
                    e.Appearance.BackColor = Color.PaleGreen;
                }

            }
        }
    }
}
